# -*- coding: utf-8 -*-
"""
Created on Mon Dec 2 20:52:33 2019

@author jeason
"""

## 1. 餐饮销售额数据异常值检测代码
import pandas as pd

catering_sale = './data/catering_sale.xls'
data = pd.read_excel(catering_sale, index_col='日期') # 载入数据制定日期为索引列

import matplotlib.pyplot as plt # 导入pyplot函数
plt.rcParams['font.sans-serif'] = ['SimHei'] #调整字体用来显示中文标签
plt.rcParams['axes.unicode_minus'] = False #用来正常显示负号

plt.figure() # 建立图像
p = data.boxplot(return_type='dict') # 绘制箱式图
x = p['fliers'][0].get_xdata() # 异常值的标签
y = p['fliers'][0].get_ydata()
y.sort() #从小到大排列

# 使用annotate添加注释并调整
for i in range(len(x)) :
    if i > 0 :
        plt.annotate(y[i], xy = (x[i], y[i]), xytext = (x[i] + 0.05 - 0.8/(y[i]-y[i-1]), y[i]))
    else :
        plt.annotate(y[i], xy = (x[i], y[i]), xytext = (x[i] + 0.08, y[i]))

plt.show() # 展示箱线图


## 2. 餐饮销量数据统计量分析代码

import pandas as pd

catering_sale = './data/catering_sale.xls'
data = pd.read_excel(catering_sale, index_col='日期')

data = data[(data['销量'] > 400) & (data['销量'] < 5000)] # 过滤异常数据
statistics = data.describe() # 保存统计量

statistics.loc['range'] = statistics.loc['max'] - statistics.loc['min']
statistics.loc['var'] = statistics.loc['std']/statistics.loc['mean']
statistics.loc['dis'] = statistics.loc['75%'] - statistics.loc['25%']

print(statistics)

## 3. 菜品盈利帕累托图

import pandas as pd

dish_profit = './data/catering_dish_profit.xls' # 餐品盈利数据
data = pd.read_excel(dish_profit, index_col='菜品名')
data = data['盈利'].copy()
data.sort(ascending = False)

import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

plt.figure()
data.plot(kind = 'bar')
plt.ylabel('盈利（元）')
p = 1*data.cumsum()/data.sum()
p.plot(color = 'r', secondary_y = True, style = '-o', linewidth = 2)
plt.annotate(format(p[6], '.4%'), xy = (6, p[6]), xytext = (6*0.9, p[6]*0.9),
    arrowprops = dict(arrowstyle = "->", connectionstyle = "arc3, rad = .2"))
plt.ylabel('盈利（比例）')
plt.show()

## 3. 餐品相关性分析

catering_sale = './data/catering_sale_all.xls'
data = pd.read_excel(catering_sale, index_col = '日期')

data.corr() #计算相关性矩阵
data.corr()['百合酱蒸凤爪']


"""
函数化操作
代码说明:
program_1: 制作箱线图
data.boxplot-->数据转为箱线图的字典格式
plt.annotate-->绘图

program_2: 计算数据
range-->极差
var-->方差
dis-->四分距

program_3: 画出盈利图（比例和数值）

program_4: 计算成对相关性
data.corr()-->dataframe中相互之间的相关性
data.corr()[u'百合酱蒸凤爪'] -->dataframe某一项与其他项的相关性
"""
import os
import numpy as np

import matplotlib.pyplot as plt
import pandas as pd

def program_1(file_name):
    catering_sale = file_name
    data = pd.read_excel(catering_sale, index_col = '日期')

    plt.figure()

    # 绘制箱线图
    p = data.boxplot(return_type = 'dict')
    x = p['fliers'][0].get_xdata()
    y = p['fliers'][0].get_ydata()

    for i in range(len(x)):
        # 处理临界情况, i = 0
        temp = y[i] - y[i - 1] if i != 0 else -78/3
        # 添加注释，xy制定标注数据，xytext指定标注位置
        plt.annotate(
            y[i], xy = (x[i], y[i]), xytext = (x[i] + 0.05 - 0.8 /temp, y[i])
        )
    
    plt.show()


def program_2(file_name):
    catering_sale = file_name
    data = pd.read_excel(catering_sale, index_col = '日期')

    data = data[(data['销量'] > 400) & data['销量'] < 5000]
    statistics = data.describe()['销量']

    statistics['range'] = statistics['max'] - statistics['min']
    statistics['var'] = statistics['std'] / statistics['mean']
    statistics['dis'] = statistics['75%'] - statistics['25%']

    print(statistics)


def program_3(file_name):
    dish_profit = file_name  #餐饮菜品盈利数据
    data = pd.read_excel(dish_profit, index_col='菜品名')
    data = data['盈利'].copy()
    data.sort_values(ascending=False)

    plt.figure()
    data.plot(kind='bar')
    plt.ylabel('盈利（元）')
    p = 1.0 * data.cumsum() / data.sum()
    p.plot(color='r', secondary_y=True, style='-o', linewidth=2)
    plt.annotate(
        format(p[6], '.4%'),
        xy=(6, p[6]),
        xytext=(6 * 0.9, p[6] * 0.9),
        arrowprops=dict(arrowstyle="->", connectionstyle="arc3,rad=.2"))
    plt.ylabel('盈利（比例）')
    plt.show()

def program_4(file_name):
    catering_sale = file_name
    data = pd.read_excel(catering_sale, index_col='日期')

    data.corr()
    data.corr()['百合酱蒸凤爪']
    data['百合酱蒸凤爪'].corr(data['翡翠蒸香茜饺'])